package rsj.dao;

import com.rlyy.aibase.utils.base.Node;
import com.rlyy.aibase.utils.base.TableNode;
import common.util.CommonUtil;
import org.apache.commons.lang3.StringUtils;
import rsj.db.dbutils.UniqueID;
import rsj.table.SYS_BIL_STATUS;
import rsj.table.SYS_ORGP;
import rsj.util.BilQuery;
import rsj.util.PaginationData;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class BilDao extends BaseDao{

    private static final String tableName = "sys.sys_bill";
    private static final BilDao instance = new BilDao();
    private static final String allColumns = "bill_id,bill_orgp_id,bill_org_id" +
            ",bill_prod_id,bill_prod_name,bill_month,bill_curusetotal" +
            ",bill_buytotal,bill_currenum,bill_curmonusenum,bill_confirm";

    public static BilDao getInstance() {
        return instance;
    }

    public PaginationData<TableNode> getBilList(BilQuery bilQuery) {
        PaginationData<TableNode> paginationData = new PaginationData<>();

//        StringBuilder sqlBuilder = new StringBuilder(String.format(select, "%s", tableName + " as b ", " bill_org_id = ? "));

        String fields =  allColumns + " ,o.orgp_prod_uid ";
        StringBuilder sqlBuilder = new StringBuilder(" SELECT %s FROM sys.sys_bill b LEFT JOIN sys.sys_orgp AS o ON b.bill_orgp_id = o.orgp_id where  bill_org_id = ? and o.orgp_type = '2' and o."+ SYS_ORGP.Orgp_Orgpstatus+"= '1' ");

            List params = new ArrayList();
            params.add(bilQuery.getOrg_Id());
            if(bilQuery.getOrgP_Ids().size() != 0){
            StringBuilder  qCount = new StringBuilder();

            for(int i = 0 ; i < bilQuery.getOrgP_Ids().size(); i++){
                qCount.append("?,");
            }

            String qCouStr = qCount.toString();
            sqlBuilder.append(" and bill_orgp_id in ( ").append(qCouStr, 0, qCouStr.length() -1).append(")");

            params.addAll(bilQuery.getOrgP_Ids());
        }
        if(StringUtils.isNotBlank(bilQuery.getStart_Date()) && StringUtils.isNotBlank(bilQuery.getEnd_Date())){
            sqlBuilder.append(" and bill_month >= ? and bill_month <= ? ");
            params.add(bilQuery.getStart_Date());
            params.add(bilQuery.getEnd_Date());
        }

        invokeSql(conn -> {

            String queryLSql = new StringBuilder(String.format(sqlBuilder.toString(), fields))
                    .append(" order by bill_month desc ")
                    .append(" limit ").append(bilQuery.getLimit())
                    .append(" offset ").append(bilQuery.getOffset())
                    .toString();
            TableNode tableNode = getQueryRunner().queryLinkTableNode(conn, queryLSql, params.toArray());

            String queryCSql = String.format(sqlBuilder.toString(), " count(1) ");
            Node countNode = getQueryRunner().queryLinkNode(conn, queryCSql, params.toArray());

            paginationData.setTotalCount(countNode.valueInt());
            paginationData.setDataList(tableNode);
            return paginationData;
        });


        return paginationData;
    }

    public String updateConfirmStatus(String bilId) {
        String sql = String.format(updateSql, tableName, " bill_confirm = ? "
                , " bill_id = ? ");
        // 账单确认 1 ，没有确认 0
        invokeSql(conn -> {
            return getQueryRunner().update(conn, sql, SYS_BIL_STATUS.conf, bilId);
        });

        return bilId;
    }

    public String updateConfirmStatus(String bilId, Connection conn) {
        String sql = String.format(updateSql, tableName, " bill_confirm = ? "
                , " bill_id = ? ");
        // 账单确认 1 ，没有确认 0
        getQueryRunner().update(conn, sql, SYS_BIL_STATUS.conf, bilId);

        return bilId;
    }

    public int insertBilCurMonth(String orgId, final Map<String, Object> map) {
        return (int) invokeSql(conn -> {
            String sql = String.format(insertOrUpdateSql, tableName, "bill_id,bill_orgp_id,bill_org_id,bill_prod_id" +
                            ",bill_prod_name,bill_month,bill_curusetotal,bill_buytotal,bill_currenum,bill_confirm"
                    , "?,?,?,?,?,?,?,?,%s,?", "bill_orgp_id,bill_month", "bill_curusetotal=?,bill_currenum=%s");

            String orgp_id = (String) map.get("Orgp_Id");// 合同id
            // orgId
            String orgp_prod_id = (String) map.get("Orgp_Prod_Id");
            String orgp_prod_name = (String) map.get("Orgp_Prod_Name");
            String currMonth = new SimpleDateFormat("yyyy-MM").format(new Date());
            long bill_curusetotal = CommonUtil.safeLong((String) map.get("Orgp_Useamount"));
            long bill_buytotal = CommonUtil.safeLong((String) map.get("Orgp_Buyamount"));
            // orgp_billmethod
            long bill_currenum = bill_buytotal - bill_curusetotal;
            if(bill_currenum < 0){
                bill_currenum = 0L;
            }
            // 0

            sql = String.format(sql, bill_currenum, bill_currenum);

            return getQueryRunner().update(conn, sql,
                    UniqueID.generateId(),
                    orgp_id, orgId, orgp_prod_id, orgp_prod_name
                    , currMonth, bill_curusetotal, bill_buytotal,
                    SYS_BIL_STATUS.confNo, bill_curusetotal
            );
        });
    }

    public void updateCure(Connection conn) throws SQLException {
        getQueryRunner().update(conn, "UPDATE sys.sys_bill SET bill_curmonusenum " +
                " = b.bill_current FROM " +
                " ( SELECT bill_id AS ID,(bill_curusetotal - LAG ( bill_curusetotal, 1, 0 ) " +
                " OVER ( PARTITION BY bill_orgp_id ORDER BY bill_month )) AS bill_current " +
                " FROM sys.sys_bill as bill ) AS b WHERE sys_bill.bill_id = b.ID;");
    }
}
